package com.econ.springboot.tdengine.demo.mybatis.service;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.util.StrUtil;
import com.econ.springboot.tdengine.demo.mybatis.domain.TagData;
import com.econ.springboot.tdengine.demo.mybatis.domain.TagType;
import com.taosdata.jdbc.TSDBPreparedStatement;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.lang.reflect.Type;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 使用原生JDBC进行数据处理
 * 在插入等功能上，减少sql损耗
 */
@Service
public class TagServiceWithJDBC {
    private String host = "10.88.12.70";
    private String dbName = "syncdb";
    private String user = "root";
    private String password = "taosdata";

    private static String datePattern = "yyyy-MM-dd HH:mm:ss.SSS";

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }


    //    private Connection connection;

//    @PostConstruct
//    private void init() {
//
//        final String url = "jdbc:TAOS://" + host + ":6030/?user=" + user + "&password=" + password;
//        // get connection
//        try {
//            Properties properties = new Properties();
//            properties.setProperty("charset", "UTF-8");
//            properties.setProperty("locale", "en_US.UTF-8");
//            properties.setProperty("timezone", "UTC-8");
//            System.out.println("get connection starting...");
//            connection = DriverManager.getConnection(url, properties);
//            if (connection != null)
//                System.out.println("[ OK ] Connection established.");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//    }

    /**
     * 压缩方式
     *
     * @param dbName
     * @param comp
     */
    public void init(String dbName, int comp) {
        this.dbName = dbName;
        dropDatabase();
        createDatabase(comp);
        createSupTable();
    }

    public void dropDatabase() {
        String sql = " drop database if exists " + dbName;
        execute(sql);
    }

    public void createDatabase(int comp) {
//        String sql = " create database if not exists " + dbName + " keep  3650 duration 10 buffer 256 wal_level 1 cachemodel last_value ";
        String sql = " create database if not exists " + dbName + " keep  3650 duration 10 buffer 256 comp " + comp + " wal_level 1 cachemodel 'last_value' ";
        execute(sql);
    }


    public List<TagData> getReal() {
        String sql = "select last_row(*),tagId from stag  group by tagId";
        execute(sql);

        return new ArrayList<>();
    }

    public void useDatabase() {
        String sql = "use " + dbName;
        execute(sql);
    }

    public void dropTable(String tbName) {
        final String sql = "drop table if exists " + dbName + "." + tbName + "";
        execute(sql);
    }

    public void createSupTable() {
        final String sql = " create table if not exists " + dbName + ".stagdouble(ts timestamp,val double, quality tinyint) tags(tagId bigint)";
        execute(sql);
        //开关量
        final String sql2 = " create table if not exists " + dbName + ".stagbool(ts timestamp,val bool, quality tinyint) tags(tagId bigint)";
        execute(sql2);
        //float
        final String sql3 = " create table if not exists " + dbName + ".stagfloat(ts timestamp,val float, quality tinyint) tags(tagId bigint)";
        execute(sql3);
        //int
        final String sql4 = " create table if not exists " + dbName + ".stagint(ts timestamp,val int, quality tinyint) tags(tagId bigint)";
        execute(sql4);
    }

    public void createTable(String tbName, int tagId, TagType type) {
        String stag = getSTableName(type);
        final String sql = "create table if not exists " + dbName + "." + tbName + " using " + stag + " tags(" + tagId + ")";
        execute(sql);
    }

    private String getSTableName(TagType type) {
        String stag = "";
        switch (type) {
            case DOUBLE:
            case BOOL:
            case INT:
            case FLOAT:
                stag = dbName + ".stag" + type.name().toLowerCase();
                break;
        }
        return stag;
    }

    public void createBatchTable(Map<Long, String> tagMap, TagType type) throws Exception {
        if (tagMap.size() > 5000) {
            throw new Exception("无法批量插入大于5000个表,请分批插入。");
        }

        String sTableName = getSTableName(type);

        String sql = "create table ";
        for (Long tagId : tagMap.keySet()) {
            String tbName = tagMap.get(tagId);
            sql += "if not exists " + dbName + "." + tbName + " using " + sTableName + " tags(" + tagId + ")";
        }

        execute(sql);
    }

    public void insert(String tbName) {
        final String sql = "insert into " + dbName + "." + tbName + " (ts, val, quality) values(now, 20.5, 34)";
        execute(sql);
    }

    private Connection getConnection() throws SQLException {
        String jdbcUrl = "jdbc:TAOS://10.88.12.70:6030?user=root&password=taosdata";
        return DriverManager.getConnection(jdbcUrl);
    }


    public void insertBatchWithType(Map<String, List<TagData>> tagDataMap, TagType tagType) throws SQLException {
        try (Connection conn = getConnection()) {
            setValue(tagDataMap, conn, tagType);
        }
    }

    private void setValue(Map<String, List<TagData>> tagDataMap, Connection conn, TagType type) throws SQLException {
        String psql = "INSERT INTO ? VALUES(?, ?, ?)";

        for (String tagName : tagDataMap.keySet()) {
            try (TSDBPreparedStatement pst = (TSDBPreparedStatement) conn.prepareStatement(psql)) {
                ArrayList<Long> tss = new ArrayList<>(10000000);
                ArrayList vals = new ArrayList<>(1000000);
                ArrayList<Byte> qualities = new ArrayList<>(10000000);
                List<TagData> tagDataList = tagDataMap.get(tagName);
                for (TagData data : tagDataList) {
                    tss.add(data.getTs());
                    vals.add(data.getVal());
                    qualities.add(data.getQuality());
                }
                pst.setTableName(dbName + "." + tagName);
                pst.setTimestamp(0, tss);
                switch (type) {
                    case DOUBLE:
                        pst.setDouble(1, vals);
                        break;
                    case BOOL:
                        pst.setBoolean(1, vals);
                        break;
                    case INT:
                        pst.setInt(1, vals);
                        break;
                    case FLOAT:
                        pst.setFloat(1, vals);
                        break;
                }

                pst.setByte(2, qualities);
                long start1 = System.currentTimeMillis();
                System.out.println(tagName + "开始执行插入......");
                pst.columnDataAddBatch();
                pst.columnDataExecuteBatch();
                long end1 = System.currentTimeMillis();
                System.out.println(tagName + "插入数据用时: " + (end1 - start1));
            }
        }
    }


    public List<TagData> select(String tbName, DateTime start, DateTime end) throws SQLException {
        String sql = "select *,tagid from " + dbName + "." + tbName + " where ts>='" + start + "' and ts<='" + end + "'";
        System.out.println("sql"+ sql);
        List<TagData> tagDataList = new ArrayList<>();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                TagData tagData = new TagData();
                while (resultSet.next()) {
                    Long timestamp = resultSet.getLong(1);
                    String valString = resultSet.getString(2);
                    byte aByte = resultSet.getByte(3);
                    Long aLong = resultSet.getLong(4);
                    tagData.setTs(timestamp);
                    tagData.setVal(valString);
                    tagData.setQuality(aByte);
                    tagData.setTagId(aLong);
                    tagDataList.add(tagData);
                }
            } catch (SQLException e) {
                throw new SQLException(e);
            }

            return tagDataList;
        }
    }


    public Long count(String tbName) {
        final String sql = "select count(*) from " + dbName + "." + tbName;
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                return resultSet.getLong(0);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return null;
    }


    private void executeQuery(String sql) {
        long start = System.currentTimeMillis();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);
                long end = System.currentTimeMillis();
                printSql(sql, true, (end - start));
                printResult(resultSet);
            } catch (SQLException e) {
                long end = System.currentTimeMillis();
                printSql(sql, false, (end - start));
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private void printResult(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String columnLabel = metaData.getColumnLabel(i);
                String value = resultSet.getString(i);
                System.out.printf("%s: %s\t", columnLabel, value);
            }
            System.out.println();
        }
    }

    private void printSql(String sql, boolean succeed, long cost) {
        System.out.println("[ " + (succeed ? "OK" : "ERROR!") + " ] time cost: " + cost + " ms, execute statement ====> " + sql);
    }

    private void execute(String sql) {
        long start = System.currentTimeMillis();
        try (Connection connection = getConnection()) {
            try (Statement statement = connection.createStatement()) {
                boolean execute = statement.execute(sql);
                long end = System.currentTimeMillis();
                printSql(sql, true, (end - start));
            } catch (SQLException e) {
                long end = System.currentTimeMillis();
                printSql(sql, false, (end - start));
                e.printStackTrace();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void printHelp() {
        System.out.println("Usage: java -jar JDBCDemo.jar -host <hostname>");
        System.exit(0);
    }

}
